using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Threading;
using FrameworkQ.Collections;
using FrameworkQ.ObjectSpace.Data.Schema;
using FrameworkQ.ObjectSpace.Data.Providers;
using FrameworkQ.ObjectSpace.Data.SqlObjects;
using FrameworkQ.ObjectSpace.Util;
using System.Data.Common;


namespace FrameworkQ.ObjectSpace
{
	/// <summary>
	/// Basic fucntional container for a object type.
	/// Each object type has a object data class. This is the 
	/// class that provides CRUD and other functionalities for 
	/// that specific class type.
	/// Anyone who wants custom execution could inherrit from
	/// this class and write their own implementation
	/// </summary>
	public class ObjectDataAccess
	{
		private Type _type = null;
		private ActionCache _actionCache = null;
		private ObjectSchema _schema = null;
		private IPopulateObject _populator =null;
		private DatabaseConnection _dbConnection =null;
        private Configuration _configuration = null;
		        

		public sealed class PredefinedActions
		{
			public const string INSERT = "INSERT";
			public const string SELECT = "SELECT";
			public const string SELECT_ALL = "SELECT_ALL";
            public const string SELECT_TOP_BY = "SELECT_ALL";
            public const string SELECT_BY = "SELECT_ALL";
			public const string UPDATE = "UPDATE";
			public const string DELETE = "DELETE";
			public const string DELETE_ALL = "DELETE_ALL";
            public const string DELETE_BY = "DELETE_BY";
			public const string SELECT_PARAM = "SELECT_PARAM";
			public const string SELECT_SINGLE_PARAM = "SELECT_SINGLE_PARAM";
			public const string SELECT_PARAM_OVERLOAD = "SELECT_PARAM_OVERLOAD";
			public const string SELECT_SINGLE_PARAM_OVERLOAD = "SELECT_SINGLE_PARAM_OVERLOAD";				
		}			

		public ObjectDataAccess(Type objectType, DatabaseConnection connection, Configuration configuration)
		{
            _type = objectType;
			_dbConnection = connection;
            _configuration = configuration;
			_actionCache = new ActionCache();
			// Create a schema for the object
            _schema = new ObjectSchema(_type, connection);
			// Get a object populator object
			_populator = ObjectDataAccess.GetPopulator (objectType, _schema.Mapping);			
		}

		
		// Caches of actions
		internal ActionCache ActionCache
		{
			get
			{
				return _actionCache;
			}
		}


		public ObjectSchema Schema
		{
			get
			{
				return _schema;
			}
		}

		// Default Actions
		public virtual int Insert (object obj)
		{
			ActionCommand cmd=null;
			
			// Get insert commad if not loaded
			if (!_actionCache.Contains(PredefinedActions.INSERT))
			{
				ActionCommandBuilder commandBuilder = new ActionCommandBuilder (_dbConnection, _configuration );
				cmd = commandBuilder.CreateInsertCommand (_schema);				
				_actionCache.Add (cmd);
			}

			cmd = _actionCache[PredefinedActions.INSERT];
            bool hasIdentity = false;
			
            // Strategy 
            if (_schema.Mapping.ContainsEncrtypedProperties)
            {
                foreach (object key in _schema.Mapping.Keys)
                {
                    PropertyMap pmap = (PropertyMap)_schema.Mapping[key];
                    if (!pmap.IsIdentity)
                    {
                        DbParameter parm = (DbParameter)cmd.Command.Parameters["@" + pmap.ColumnName];
                        parm.Value = pmap.Property.GetValue(obj, (object[])null);
                    }
                }
            }
            else
            {
                foreach (object key in _schema.Mapping.Keys)
                {
                    PropertyMap pmap = (PropertyMap)_schema.Mapping[key];
                    if (!pmap.IsIdentity)
                    {
                        DbParameter parm = (DbParameter)cmd.Command.Parameters["@" + pmap.ColumnName];
                        if (pmap.IsEncrypted)
                        {
                            string encryptionKey = pmap.EncrtyptionKey;
                            if (pmap.UseEncryptionKeyFromConfig)
                            {
                                encryptionKey = System.Configuration.ConfigurationSettings.AppSettings[encryptionKey];
                            }
                            AESCryptoHelper helper = new AESCryptoHelper(encryptionKey);
                            object propertyValue = pmap.Property.GetValue(obj, (object[])null);
                            if (propertyValue==null)
                            {
                                parm.Value = propertyValue;
                            }
                            else
                            {
                                string stringValue = (string)propertyValue;
                                parm.Value = helper.Encrypt(stringValue);
                            }
                            helper = null;
                        }
                        else
                        {                            
                            parm.Value = pmap.Property.GetValue(obj, (object[])null);
                        }
                    }
                    else
                    {
                        hasIdentity = true;
                    }
                }
            }

			IDbConnection conn = _dbConnection.Open();
			cmd.Command.Connection = conn;
			cmd.Command.Transaction =_dbConnection.Transaction;
		
			object ret = cmd.Command.ExecuteScalar();
            _dbConnection.Close();
            if( null == ret || !hasIdentity  )
			{
				// Return -1 because nither @@ROWCOUNT not @@IDENTITY will be negative
				return -1;
			}
			else
			{

				// SQL Server returns decimal which needs to be converted to 32bit int
				return Int32.Parse( ret.ToString());				
			}
		}

		private void SetValue ( PropertyInfo prop, object obj, object value)
		{
			object objSet;
			if (prop.PropertyType.Equals(typeof(int)))
			{
				objSet = Convert.ToInt32 (value);
			}
			else
			{
				objSet = value;
			}
			prop.SetValue ( obj, objSet , (object[]) null);
            
		}

		public virtual object Update (object obj)
		{
			ActionCommand cmd=null;
			// Get insert commad if not loaded
			if (!_actionCache.Contains(PredefinedActions.UPDATE))
			{
                ActionCommandBuilder commandBuilder = new ActionCommandBuilder(_dbConnection, _configuration);
				cmd = commandBuilder.CreateUpdateCommand(_schema);				
				_actionCache.Add (cmd);
			}
			
			cmd = _actionCache[PredefinedActions.UPDATE];

			
			PropertyMap primary = _schema.Mapping.Primary;

            // Strategy 
            if (_schema.Mapping.ContainsEncrtypedProperties)
            {
                foreach (object key in _schema.Mapping.Keys)
                {
                    PropertyMap pmap = (PropertyMap)_schema.Mapping[key];
                    if (!PropertyMap.ReferenceEquals(primary, pmap))
                    {
                        DbParameter parm = (DbParameter)cmd.Command.Parameters["@" + pmap.ColumnName];
                        parm.Value = pmap.Property.GetValue(obj, (object[])null);
                    }
                }
            }
            else
            {
                foreach (object key in _schema.Mapping.Keys)
                {
                    PropertyMap pmap = (PropertyMap)_schema.Mapping[key];

                }
            }

            // Strategy 
            if (_schema.Mapping.ContainsEncrtypedProperties)
            {
                foreach (object key in _schema.Mapping.Keys)
                {
                    PropertyMap pmap = (PropertyMap)_schema.Mapping[key];
                    if (!pmap.IsIdentity)
                    {
                        DbParameter parm = (DbParameter)cmd.Command.Parameters["@" + pmap.ColumnName];
                        parm.Value = pmap.Property.GetValue(obj, (object[])null);
                    }
                }
            }
            else
            {
                foreach (object key in _schema.Mapping.Keys)
                {
                    PropertyMap pmap = (PropertyMap)_schema.Mapping[key];
                    DbParameter parm = (DbParameter)cmd.Command.Parameters["@" + pmap.ColumnName];
                    if (pmap.IsEncrypted)
                    {
                        string encryptionKey = pmap.EncrtyptionKey;
                        if (pmap.UseEncryptionKeyFromConfig)
                        {
                            encryptionKey = System.Configuration.ConfigurationSettings.AppSettings[encryptionKey];
                        }
                        AESCryptoHelper helper = new AESCryptoHelper(encryptionKey);
                        object propertyValue = pmap.Property.GetValue(obj, (object[])null);
                        if (propertyValue == null)
                        {
                            parm.Value = propertyValue;
                        }
                        else
                        {
                            string stringValue = (string)propertyValue;
                            parm.Value = helper.Encrypt(stringValue);
                        }
                        helper = null;
                    }
                    else
                    {
                        parm.Value = pmap.Property.GetValue(obj, (object[])null);
                    }
                }
            }


			DbParameter parmID = (DbParameter) cmd.Command.Parameters["@" + primary.ColumnName ];
			parmID.Value = primary.Property.GetValue(obj,(object[])null);
			
			IDbConnection conn = _dbConnection.Open ();
			cmd.Command.Connection = conn;
			cmd.Command.Transaction =_dbConnection.Transaction;
			object ret = cmd.Command.ExecuteNonQuery();
			
			_dbConnection.Close();

			return ret;
		}

		
		public virtual int Delete (object primaryKey)
		{
			ActionCommand cmd=null;
			// Get insert commad if not loaded
			if (!_actionCache.Contains(PredefinedActions.DELETE))
			{
                ActionCommandBuilder commandBuilder = new ActionCommandBuilder(_dbConnection, _configuration);
				cmd = commandBuilder.CreateDeleteCommand(_schema);				
				_actionCache.Add (cmd);
			}

			cmd = _actionCache[PredefinedActions.DELETE];

			PropertyMap primary = _schema.Mapping.Primary;

			DbParameter parmID = (DbParameter) cmd.Command.Parameters["@" + primary.ColumnName];
			parmID.Value = primaryKey;
			
			IDbConnection conn = _dbConnection.Open();

			cmd.Command.Connection = conn;
			cmd.Command.Transaction =_dbConnection.Transaction;
			int ret =  cmd.Command.ExecuteNonQuery();
			_dbConnection.Close();
			return ret;
		}

		public virtual void DeleteAll ()
		{
			ActionCommand cmd=null;
			// Get insert commad if not loaded
			if (!_actionCache.Contains(PredefinedActions.DELETE_ALL))
			{
                ActionCommandBuilder commandBuilder = new ActionCommandBuilder(_dbConnection, _configuration);
				cmd = commandBuilder.CreateDeleteAllCommand(_schema);				
				_actionCache.Add (cmd);
			}

			cmd = _actionCache[PredefinedActions.DELETE_ALL];

			IDbConnection conn = _dbConnection.Open();

			cmd.Command.Connection = conn;
			cmd.Command.Transaction =_dbConnection.Transaction;
			cmd.Command.ExecuteNonQuery();
			_dbConnection.Close();
		}
		
	  
	  public virtual object Select (object primaryKey)
		{
			ActionCommand cmd=null;
			// Get insert commad if not loaded
			if (!_actionCache.Contains(PredefinedActions.SELECT ))
			{
                ActionCommandBuilder commandBuilder = new ActionCommandBuilder(_dbConnection, _configuration);
				cmd = commandBuilder.CreateSelectCommand(_schema);				
				_actionCache.Add (cmd);
			}

			cmd = _actionCache[PredefinedActions.SELECT];

			PropertyMap primary = _schema.Mapping.Primary ;

			DbParameter parmID = (DbParameter) cmd.Command.Parameters["@" + primary.ColumnName];
			parmID.Value = primaryKey;
			
			IDbConnection conn = _dbConnection.Open();

			cmd.Command.Connection = conn;
			cmd.Command.Transaction =_dbConnection.Transaction;
			IDataReader reader = cmd.Command.ExecuteReader();
			object obj = null;
			if (reader.Read())
			{
				obj =  Activator.CreateInstance (_type);
				_populator.PopulateObject (obj, reader);
			}
            reader.Close();
			_dbConnection.Close();
			return obj;
		}



		public virtual IList SelectAll ( Type collectionType )
		{
			ActionCommand cmd=null;
			// Get insert commad if not loaded
			if (!_actionCache.Contains(PredefinedActions.SELECT_ALL ))
			{
                ActionCommandBuilder commandBuilder = new ActionCommandBuilder(_dbConnection, _configuration);
				cmd = commandBuilder.CreateSelectAllCommand(_schema);				
				_actionCache.Add (cmd);
			}

			cmd = _actionCache[PredefinedActions.SELECT_ALL];

			IDbConnection conn = _dbConnection.Open();

			Console.WriteLine (Thread.CurrentThread.Name);
			cmd.Command.Connection = conn;
			cmd.Command.Transaction =_dbConnection.Transaction;
			IDataReader reader = cmd.Command.ExecuteReader();		
			IList list = SelectFromReader (reader , collectionType , null);
            reader.Close();
			_dbConnection.Close();
			return list;
		}


		public virtual IList SelectBy ( WhereClause wherePhrase, OrderByItemCollection orderByItems )
		{
			ActionCommand cmd=null;
            ActionCommandBuilder commandBuilder = new ActionCommandBuilder(_dbConnection, _configuration);
			cmd = commandBuilder.CreateSelectByCommand (_schema, wherePhrase, orderByItems);	
			
			IDbConnection conn = _dbConnection.Open();

			cmd.Command.Connection = conn;
			cmd.Command.Transaction =_dbConnection.Transaction;
			IDataReader reader = cmd.Command.ExecuteReader();
			IList list = SelectFromReader (reader, null);
            reader.Close();
			_dbConnection.Close();
			return list;
		}

        public virtual IList SelectBy(string wherePhrase, string orderBy)
        {
            ActionCommand cmd = null;
            ActionCommandBuilder commandBuilder = new ActionCommandBuilder(_dbConnection, _configuration);
            cmd = commandBuilder.CreateSelectByCommand(_schema, wherePhrase, orderBy);

            IDbConnection conn = _dbConnection.Open();

            cmd.Command.Connection = conn;
            cmd.Command.Transaction = _dbConnection.Transaction;
            IDataReader reader = cmd.Command.ExecuteReader();
            IList list = SelectFromReader(reader, null);
            reader.Close();
            _dbConnection.Close();
            return list;
        }



        public virtual int DeleteBy(WhereClause wherePhrase)
        {
            ActionCommand cmd = null;
            ActionCommandBuilder commandBuilder = new ActionCommandBuilder(_dbConnection, _configuration);
            cmd = commandBuilder.CreateDeleteByCommand(_schema, wherePhrase );

            IDbConnection conn = _dbConnection.Open();

            cmd.Command.Connection = conn;
            cmd.Command.Transaction = _dbConnection.Transaction;
            // return
            return cmd.Command.ExecuteNonQuery();
        }



		public virtual IList SelectTopBy ( int top, WhereClause wherePhrase, OrderByItemCollection orderByItems )
		{
			ActionCommand cmd=null;
            ActionCommandBuilder commandBuilder = new ActionCommandBuilder(_dbConnection, _configuration);
			cmd = commandBuilder.CreateSelectTopByCommand (_schema,top,  wherePhrase , orderByItems);	
			
			IDbConnection conn = _dbConnection.Open();

			cmd.Command.Connection = conn;
			cmd.Command.Transaction =_dbConnection.Transaction;
			IDataReader reader = cmd.Command.ExecuteReader();			
			IList list = SelectFromReader (reader, null);
            reader.Close();
			_dbConnection.Close();
			return list ;
		}

		public virtual IList SelectTopBy ( int top, OrderByItemCollection orderBy )
		{
			return this.SelectTopBy (top, null , orderBy );
		}


		public virtual IList SelectBySQL (string sql)
		{
			return SelectBySQL (sql,null);
		}

		public virtual IList SelectBySQL (string sql, PropertyMaps mappings)
		{			
			IDbConnection conn = _dbConnection.Open();
			IDbCommand cmd = DBObjectsFactory.CreateCommand (_configuration.Provider, sql, conn, _dbConnection.Transaction);
			IDataReader reader = cmd.ExecuteReader();			
			IList list = SelectFromReader (reader, mappings) as IList;
            reader.Close();
			_dbConnection.Close();
			return list;

		}

		public virtual IList SelectFromReader (IDataReader reader, PropertyMaps mappings)
		{
			IPopulateObject populator = null;
			ObjectList list = new ObjectList();			
			if (mappings ==null)
			{
				populator = _populator;
			}
			else
			{
				populator = ObjectDataAccess.GetPopulator(_type, mappings);
			}
			while(reader.Read())
			{
				object obj =  Activator.CreateInstance (_type);
				populator.PopulateObject (obj, reader);
				list.Add (obj);
			}
			return list;
		}
		public virtual IList SelectFromReader (IDataReader reader, Type collectionType, PropertyMaps mappings)
		{
			IPopulateObject populator = null;
			IList list = (IList) Activator.CreateInstance( collectionType );
			if (mappings ==null)
			{
				populator = _populator;
			}
			else
			{
				populator = ObjectDataAccess.GetPopulator(_type, mappings);
			}
			while(reader.Read())
			{
				object obj =  Activator.CreateInstance (_type);
				populator.PopulateObject (obj, reader);
				list.Add (obj);
			}
			return list;
		}


		public virtual object SelectSingleFromReader (IDataReader reader, PropertyMaps mappings)
		{
			IPopulateObject populator = null;
			if (mappings ==null)
			{
				populator = _populator;
			}
			else
			{
				populator = ObjectDataAccess.GetPopulator(_type, mappings);
			}
			if(reader.Read())
			{
				object obj =  Activator.CreateInstance (_type);
				populator.PopulateObject (obj, reader);
				
				return obj;
			}
			else
			{
				return null;
			}
		}


        object PopulateObject(IDataReader reader)
        {
            throw new NotImplementedException();
        }
        
        bool RequiresObjectCreation
        {
            get { return true; }
        }
		
        public void PopulateObject (object obj, IDataReader reader)
		{
			_populator.PopulateObject (obj, reader);
		} 
		
		private static IPopulateObject GetPopulator (Type type, PropertyMaps mappings)
		{
			// Check if the object have custom populator
			CustomObjectPopulatorAttribute attr = null;
			object[] customAttributes =  type.GetCustomAttributes(typeof(CustomObjectPopulatorAttribute),false);
			if (customAttributes.Length>0)
			{
				attr = (CustomObjectPopulatorAttribute) customAttributes[0];
			}						

			IPopulateObject populator;

			if (attr==null)
			{
                ConcretePopulatorAttribute attrConcretePop = CommonHelper.GetAttribute<ConcretePopulatorAttribute>(type);
                if (attr != null)
                {
                    populator = new ConcretePopulator(attrConcretePop.MethodHostType, attrConcretePop.Method);
                }
                else
                {
                    populator = new ObjectPopulator(mappings);
                }
			}
			else
			{
				populator  = (IPopulateObject) Activator.CreateInstance (attr.PopulatorType);
			}			

			return populator;
		}		
	}
}
